package com.house.reportisy;

import com.house.dto.BillDTO;
import com.house.entity.Bill;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Map;


/**
 * @author: GGP
 * @description: 订单DAO
 * @time: 2023-04-16 11:21
 **/
public interface BillRepository extends JpaRepository<Bill,Integer>, JpaSpecificationExecutor<Bill> {
	/**
	 * 根据用户查询所有订单-前台
	 * @param userId
	 * @return
	 */
	@Query(value = "select b.id,h.id as houseId,h.house_address as address,r.number as roomNum,b.bill_number as billNumber,b.price,b.check_in_time as checkInTime,b.check_out_time as checkOutTime,DATE_FORMAT(b.bill_time,'%Y-%m-%d %H:%I:%s') as billTime,b.score,b.status"+
			" from bill AS b left JOIN room AS r on b.room_id=r.id JOIN house AS h ON b.house_id = h.id"+
			" where b.user_id= :userId and b.del_flag=0",nativeQuery = true)
	Page<Map<String,Object>> findBillByUserId(@Param("userId") Integer userId, Pageable pageable);

	/**
	 * 查询订单-后台:查询此房东所有订单，基础上加状态、房间号查
	 * @param
	 * @return
	 */
	@Query(value = "select b.id,b.bill_number as billNumber,b.house_id as houseId,b.user_id as userId,b.room_id as roomId,b.price,b.uname,b.phone,b.idcard as idCard,b.check_in_time as checkInTime,b.check_out_time as checkOutTime,b.status,DATE_FORMAT(b.bill_time,'%Y-%m-%d %H:%I:%s') as billTime,b.score"+
			" from bill as b"+
			" join house as h on b.house_id=h.id"+
			" where IF(:#{#billDTO.publisherId} is not null,h.publisher_id=:#{#billDTO.publisherId},1=1)"+
			" and IF(:#{#billDTO.status} is not null,b.status=:#{#billDTO.status},1=1)"+
			" and b.del_flag=0", nativeQuery = true)
	Page<Map<String,Object>> findBills(@Param("billDTO")BillDTO billDTO,Pageable pageable);

	/**
	 * 房间号查询所有订单
	 * @param pageable
	 * @return
	 */
	@Query(value = "select b.id,b.bill_number as billNumber,b.user_id as userId,b.room_id as roomId,b.price,b.uname,b.phone,b.idcard as idCard,b.check_in_time as checkInTime,b.check_out_time as checkOutTime,b.status,DATE_FORMAT(b.bill_time,'%Y-%m-%d %H:%I:%s') as billTime,b.score"+
	" from bill as b join room as r on b.room_id=r.id join house as h"+
	" where h.publisher_id=:#{#billDTO.publisherId}"+
	" and r.number= :#{#billDTO.roomNum}"+
	" and b.del_flag=0",nativeQuery = true)
	Page<Map<String,Object>> findByRoomNum(@Param("billDTO")BillDTO billDTO,Pageable pageable);

	/**
	 * 获取所有评分
	 * @return
	 */
	@Query(value = "SELECT SUM( b.score ) AS totalScore,COUNT( * ) AS totalCount" +
			" FROM bill AS b JOIN house AS h ON b.house_id = h.id" +
			" WHERE b.STATUS >= 4 AND b.house_id =:houseId", nativeQuery = true)
	Map<String,Object> findAllScore(@Param("houseId") Integer houseId);

	/**
	 * 查询近一个月的总额
	 * @param publisherId
	 * @return
	 */
	@Query(value = "SELECT sum(b.price)" +
			" from bill as b join house as h" +
			" where h.publisher_id=:publisherId" +
			" AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(b.bill_time);",nativeQuery = true)
	String grossAmount(@Param("publisherId")Integer publisherId);

	/**
	 * 查询总额度
	 * @param publisherId
	 * @return
	 */
	@Query(value = "SELECT sum(b.price)" +
			" from bill as b join house as h"+
			" where h.publisher_id=:publisherId",nativeQuery = true)
	String grossAmounts(@Param("publisherId")Integer publisherId);
}
